﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.ComponentModel.DataAnnotations;
using System.Xml.Serialization;
using System.Runtime.Serialization;
using System.Text;
using ParieurPro.DAL.DatabaseModel;
using ParieurPro.DAL.Exception;
using System.Data.SqlClient;
using System.Web.Security;

namespace ParieurPro.DAL
{

    /// <summary>
    /// Classe de gestion d'un joueur.
    /// Un joueur peut être :
    ///     - administrateur général : 
    ///         - capacité à mettre à jour les scores, 
    ///         - changer les dates d'ouverture/fermeture des événements
    ///     - administrateur d'un pari :
    ///         - capacité de modifier les paramètres du tournoi (ligue, mode de comptage des scores)
    ///         - capacité d'ajouter/supprimer des participants au tournoi
    ///     - participant à un tournoi
    ///         - pari sur un journée
    ///         - consulter ses stats et le classement
    ///         - consulter les paris des autres joueurs une fois que les paris sont clos
    ///     - visiteur
    ///         - rejoindre un tournoi
    ///         - lire les conditions générales du jeu
    /// </summary>
    [DataContractAttribute(IsReference = true)]
    [Serializable()]
    public class UserObject
    {
        /// <summary>
        /// Liste des rôles
        /// </summary>
        public const string AdministratorRole = "Administrator";
        public const string BetManagerRole = "BetManager";
        public const string PlayerRole = "Player";
        public const string RegisteredUsersRole = "Registered Users";

        #region Attributes
        private string _userName;
        private Guid _userId;
        private string _email;
        private int _currentContestChampionshipId;
        private List<int> _championshipList;
        private int _currentContestSeasonId;
        private int _currentContestId;
        private int? _rank;
        private int _customContestId;
        private int? _averageScore;
        private int? _maxScore;
        private int? _minScore;
        private int? _betCount;
        private int? _maximumBetCount;
        private Int64? _totalScore;
        private DateTime? _creationDate;
        
        private string _passwordQuestion;
        private int? _bestOfEventCount;
        private int _customContestPlayerCount;
        private int _ContestAdministratorCount;

        private List<int> _rankEvolution;
        #endregion

        #region Properties

        /// <summary>
        /// Identifiant unique du joueur
        /// </summary>
        [Key()]
        [DataMemberAttribute()]
        public Guid UserId
        {
            get { return _userId; }
            set { _userId = value; }
        }

        /// <summary>
        /// Login du joueur
        /// </summary>
        [DataMemberAttribute()]
        public string UserName
        {
            get { return _userName; }
            set { _userName = value; }
        }

        /// <summary>
        /// Adresse mail du joueur
        /// </summary>
        [DataMemberAttribute()]
        public string Email
        {
            get { return _email; }
            set { _email = value; }
        }

        /// <summary>
        /// Classement du joueur
        /// </summary>
        [DataMemberAttribute()]
        public int? Rank
        {
            get { return _rank; }
            set { _rank = value; }
        }

        /// <summary>
        /// Identifiant du championnat courant
        /// </summary>
        [DataMemberAttribute()]
        public int CurrentContestChampionshipId
        {
            get { return _currentContestChampionshipId; }
            set { _currentContestChampionshipId = value; }
        }

        /// <summary>
        /// Identifiant de la saison en cours
        /// </summary>
        [DataMemberAttribute()]
        public int CurrentContestSeasonId
        {
            get { return _currentContestSeasonId; }
            set { _currentContestSeasonId = value; }
        }

        /// <summary>
        /// Liste des championnats disponibles dans le tournoi
        /// </summary>
        [DataMemberAttribute()]
        public List<int> ChampionshipList
        {
            get { return _championshipList; }
            set { _championshipList = value; }
        }

        /// <summary>
        /// Identifiant de la compétition en cours
        /// </summary>
        [DataMemberAttribute()]
        public int CurrentContestId
        {
            get { return _currentContestId; }
            set { _currentContestId = value; }
        }

        /// <summary>
        /// Identifiant du tournoi auquel le joueur participe
        /// </summary>
        [DataMemberAttribute()]
        public int CustomContestId
        {
            get { return _customContestId; }
            set { _customContestId = value; }
        }

        /// <summary>
        /// Score moyen du joueur
        /// </summary>
        [DataMemberAttribute()]
        public int? AverageScore
        {
            get { return _averageScore; }
            set { _averageScore = value; }
        }

        /// <summary>
        /// Score maximum du joueur
        /// </summary>
        [DataMemberAttribute()]
        public int? MaxScore
        {
            get { return _maxScore; }
            set { _maxScore = value; }
        }

        /// <summary>
        /// Score minimum du joueur
        /// </summary>
        [DataMemberAttribute()]
        public int? MinScore
        {
            get { return _minScore; }
            set { _minScore = value; }
        }

        /// <summary>
        /// Nombre de fois premier d'une journée
        /// </summary>
        [DataMemberAttribute()]
        public int? BestOfEventCount
        {
            get { return _bestOfEventCount; }
            set { _bestOfEventCount = value; }
        }

        /// <summary>
        /// Nombre d'événements pour lesquels le joueur a parié
        /// </summary>
        [DataMemberAttribute()]
        public int? BetCount
        {
            get { return _betCount; }
            set { _betCount = value; }
        }

        /// <summary>
        /// Nombre d'événements sur lesquels le joueur aurait pu parié
        /// </summary>
        [DataMemberAttribute()]
        public int? MaximumBetCount
        {
            get { return _maximumBetCount; }
            set { _maximumBetCount = value; }
        }

        /// <summary>
        /// Score du joueur
        /// </summary>
        [DataMemberAttribute()]
        public Int64? TotalScore
        {
            get { return _totalScore; }
            set { _totalScore = value; }
        }

        /// <summary>
        /// Date d'inscription
        /// </summary>
        [DataMemberAttribute()]
        public DateTime? CreationDate
        {
            get { return _creationDate; }
            set { _creationDate = value; }
        }

        /// <summary>
        /// Question pour retrouver son mot de passe
        /// </summary>
        [DataMemberAttribute()]
        public string PasswordQuestion
        {
            get { return _passwordQuestion; }
            set { _passwordQuestion = value; }
        }

        /// <summary>
        /// Liste des classements par journée.
        /// Chaque élément contient :
        ///  - clé : numéro de la journée
        ///  - valeur : classement à l'issu de la journée
        /// </summary>
        [DataMemberAttribute()]
        public List<int> RankEvolution
        {
            get { return _rankEvolution; }
            set { _rankEvolution = value; }
        }

        /// <summary>
        /// Nombre de participants au tournoi auquel le joueur
        /// participe
        /// </summary>
        [DataMemberAttribute()]
        public int CustomContestPlayerCount
        {
            get { return _customContestPlayerCount; }
            set { _customContestPlayerCount = value; }
        }

        /// <summary>
        /// Nombre de tournois pour lesquels l'utilisateur est administrateur 
        /// </summary>
        [DataMemberAttribute()]
        public int ContestAdministratorCount
        {
            get { return _ContestAdministratorCount; }
            set { _ContestAdministratorCount = value; }
        }

        #endregion

        /// <summary>
        /// Renvoie le joueur dont le login et l'adresse mail est passé en paramètre
        ///     - id
        ///     - login / email
        ///     - question pour retrouver son mot de passe
        /// </summary>
        /// <param name="dbContext">Connexion à la base de données</param>
        /// <param name="login">login</param>
        /// <param name="email">email</param>
        /// <returns>Joueur ou null si le joueur n'existe pas</returns>
        /// <exception cref="ArgumentException">dbContext vaut null</exception>
        public static UserObject GetUserFromLoginAndEmail(MyBetDBEntities dbContext, string login, string email)
        {
            UserObject returnUser = null;
            if (dbContext == null)
            {
                throw new ArgumentException("UserObject.GetUserFromLoginAndEmail - dbContext can not be null");
            }

            // Récupération du joueur en base de données
            aspnet_Membership user = (from aspUser in dbContext.aspnet_Membership
                          where ((aspUser.aspnet_Users.LoweredUserName == login.ToLower()) && (aspUser.aspnet_Applications.LoweredApplicationName == "mybet")
                          && aspUser.Email.ToLower() == email.ToLower())
                          select aspUser).SingleOrDefault();

            if (user != null)
            {
                returnUser = new UserObject();
                returnUser.UserName = login;
                returnUser.Email = email;
                returnUser.PasswordQuestion = user.PasswordQuestion;
                returnUser.UserId = user.UserId;
            }

            return returnUser;
        }

        /// <summary>
        /// Renvoie la liste des tournois auxquels l'utilisateur est inscrits
        /// </summary>
        /// <param name="dbContext">Connexion à la base de données</param>
        /// <param name="userId">GUID de l'utilisateur</param>
        /// <returns>Liste des tournois</returns>
        /// <exception cref="ArgumentException">dbContext vaut null</exception>
        /// <exception cref="userId">userId est vide</exception>
        public static List<CustomContestObject> GetSubscriptions(MyBetDBEntities dbContext, Guid userId)
        {
            if (dbContext == null)
            {
                throw new ArgumentException("UserObject.GetSubscriptions - dbContext can not be null");
            }
            if (userId == Guid.Empty)
            {
                throw new ArgumentException("UserObject.GetSubscriptions - unknown user");
            }

            //var queryBets = (from subscription in dbContext.Subscriptions
            //                 where (subscription.UserId == userId)
            //                 select subscription.CustomContest);

            //List<CustomContest> subscriptions = queryBets.ToList();


            // Récupération des informations sur le joueur
            object[] parameters1 = 
            {
                new SqlParameter { ParameterName = "p0", Value = userId }
            };
            StringBuilder sqlRequest = new StringBuilder();
            sqlRequest.Append(@"select cc.*, Subscriptions.CustomContestId AS [Id], Subscriptions.IsDefault AS [IsDefaultContest], rnk.CurrentRank, (SELECT COUNT(*) FROM Subscriptions where CustomContestId=cc.CustomContestId) AS [SubscriberCount] ");
            sqlRequest.Append("FROM Subscriptions left join CustomContest cc on cc.CustomContestId=Subscriptions.CustomContestId ");
            sqlRequest.Append("left join Ranks rnk on rnk.SubscriptionId=Subscriptions.SubscriptionId ");
            sqlRequest.AppendFormat("where Subscriptions.UserId=@p0 order by Subscriptions.SubscriptionId desc");

            IEnumerable<CustomContestObject> subscriptions = dbContext.ExecuteStoreQuery<CustomContestObject>(sqlRequest.ToString(), parameters1);
            return subscriptions.ToList();
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="dbContext"></param>
        /// <param name="userId"></param>
        /// <param name="customContestId"></param>
        /// <returns></returns>
        public static bool SetDefaultCustomContest(MyBetDBEntities dbContext, Guid userId, int customContestId)
        {
            int returnValue;
            object[] parameters = 
            {
                new SqlParameter { ParameterName = "p0", Value = userId },
                new SqlParameter { ParameterName = "p1", Value = customContestId }
            };

            try
            {
                StringBuilder sqlRequest = new StringBuilder();
                IEnumerable<int> queryResult = dbContext.ExecuteStoreQuery<int>
                    (String.Format(@"DECLARE @return_value int
                                     EXEC @return_value = [dbo].[mybet_setDefaultCustomContest]
		                             @UserId = @p0,
		                             @CustomContestId = @p1
                                     SELECT	@return_value"), parameters);

                    returnValue = queryResult.FirstOrDefault();
                    switch (returnValue)
                    {
                        case 0:
                            // Succes
                            return true;
                        default:
                            // Erreur SQL interne
                            throw new System.Exception(String.Format("JoinCustomContest - Internal Error : {0}", returnValue));
                    }
                }
                catch (System.Exception ex)
                {
                    System.Diagnostics.Trace.WriteLine(ex.ToString());
                }
            
            return false;
        }

        /// <summary>
        /// Récupération des informations d'un utilisateur à partir de son login
        ///  - GUID
        ///  - Championnat courant
        ///  - Compétition courante
        ///  - Saison courante
        ///  - Classement
        ///  - Tournoi
        ///  - Nombre de paris effectués
        ///  - Score
        ///  - Date d'inscription
        ///  - Score moyen
        ///  - Score minimum
        ///  - Score maximum
        ///  - Nombre de fois premier d'une journée de paris
        ///  - Nombre de paris maximum auxquels le joueur aurait pu participer
        /// </summary>
        /// <param name="dbContext">Connexion à la base de données</param>
        /// <param name="userLogin">joueur au format "MembershipUser"</param>
        /// <returns>joueur au format "UserObject" avec les statistiques du joueur.</returns>
        public static UserObject CopyFrom(MyBetDBEntities dbContext, String userLogin, int? CustomContestIdToLoad)
        {

            if (dbContext == null)
            {
                throw new ArgumentException("UserObject.CopyFrom - dbContext can not be null");
            }

            UserObject user = new UserObject();
            string isDefaultCondition = String.Empty;
            if (!CustomContestIdToLoad.HasValue)
            {
                isDefaultCondition = "AND (s.IsDefault=1 OR s.IsDefault IS NULL)";
            }
            else
            {
                isDefaultCondition = String.Format("AND (s.CustomContestId={0})", CustomContestIdToLoad.Value);
            }

            user.UserId = Guid.Empty;
            user.UserName = userLogin.Trim();
            user.RankEvolution = new List<int>();

            //user.Email = currentUser.Email;
            user.Rank = 0;

            // Récupération du GUID
            if (user.UserId == Guid.Empty)
            {
                aspnet_Users userQuery = (from aspUser in dbContext.aspnet_Users
                              where ((aspUser.LoweredUserName == userLogin.ToLower()) && (aspUser.aspnet_Applications.LoweredApplicationName == "mybet"))
                              select aspUser).FirstOrDefault();
                if (userQuery != null)
                {
                    user.UserId = userQuery.UserId;
                }
            }

            if (user.UserId == Guid.Empty)
            {
                // Unknown user
                return null;
            }

            // Récupération des informations sur le joueur
            object[] parameters1 = 
            {
                new SqlParameter { ParameterName = "p0", Value = user.UserId }
            };

            IEnumerable<UserObject> results = dbContext.ExecuteStoreQuery<UserObject>
                (String.Format(@"SELECT cc.CustomContestId, c.ChampionshipId as CurrentContestChampionshipId, c.ContestId as CurrentContestId, 
                c.SeasonId as CurrentContestSeasonId, rnk.CurrentRank as Rank, rnk.EventCount as BetCount, 
                rnk.TotalScore, memberShip.CreateDate as CreationDate, memberShip.Email, (SELECT COUNT(ccAdmin.CustomContestId) FROM dbo.CustomContest ccAdmin WHERE ccAdmin.AdministratorId=s.UserId) ContestAdministratorCount 
                FROM dbo.Contests as c, dbo.CustomContestLinks ccl, dbo.CustomContest cc, dbo.Subscriptions s
                LEFT OUTER JOIN dbo.Ranks rnk ON rnk.SubscriptionId = s.SubscriptionId
                LEFT JOIN dbo.aspnet_Membership memberShip ON memberShip.UserId=s.UserId
                WHERE s.CustomContestId = cc.CustomContestId 
                AND cc.CustomContestId = ccl.CustomContestId  
                AND ccl.ContestId = c.ContestId
                {0}
                AND s.UserId=@p0", isDefaultCondition), parameters1);

            foreach (UserObject sqlUser in results)
            {
                user.CurrentContestChampionshipId = sqlUser.CurrentContestChampionshipId;
                user.CurrentContestId = sqlUser.CurrentContestId;
                user.CurrentContestSeasonId = sqlUser.CurrentContestSeasonId;
                user.ChampionshipList = new List<int>();
                user.ChampionshipList.Add(user.CurrentContestChampionshipId);
                user.Rank = sqlUser.Rank;
                user.CustomContestId = sqlUser.CustomContestId;
                user.BetCount = sqlUser.BetCount;
                user.TotalScore = sqlUser.TotalScore;
                user.CreationDate = sqlUser.CreationDate;
                //user.LastLoginDate = sqlUser.LastLoginDate;
                user.Email = sqlUser.Email;
            }

            // Récupération des statistiques du joueur
            object[] parameters2 = 
            {
                new SqlParameter { ParameterName = "p0", Value = user.UserId },
                new SqlParameter { ParameterName = "p1", Value = user.CustomContestId }
            };

            IEnumerable<UserObject> statsResult = dbContext.ExecuteStoreQuery<UserObject>
                (@"SELECT AVG(EventScores.Score) as averageScore, MAX(EventScores.Score) as maxScore, MIN(EventScores.Score) as minScore, SUM(CustomContestEventScores.IsBestScore) as BestOfEventCount,
                   (SELECT COUNT(Subscriptions.UserId) FROM dbo.Subscriptions WHERE Subscriptions.CustomContestId=@p1) as CustomContestPlayerCount                
                   FROM CustomContestEventScores
                   LEFT JOIN EventScores on EventScores.UserId=CustomContestEventScores.UserId AND EventScores.EventId=CustomContestEventScores.EventId
                   WHERE CustomContestEventScores.UserId = @p0 and CustomContestEventScores.IsCompleted=1 and CustomContestEventScores.CustomContestId=@p1 ", parameters2);
            foreach (UserObject sqlStats in statsResult)
            {
                user.AverageScore = sqlStats.AverageScore;
                user.MinScore = sqlStats.MinScore;
                user.MaxScore = sqlStats.MaxScore;
                user.BestOfEventCount = sqlStats.BestOfEventCount;
                user.CustomContestPlayerCount = sqlStats.CustomContestPlayerCount;
            }

            object[] parameters3 = 
            {
                new SqlParameter { ParameterName = "p0", Value = user.UserId },
                new SqlParameter { ParameterName = "p1", Value = user.CustomContestId }
            };
            IEnumerable<UserObject> maximumBetCountResult = dbContext.ExecuteStoreQuery<UserObject>
                (@"select count(distinct evt.EventId) MaximumBetCount from UserBets ub
                   left join Subscriptions s on s.UserId=ub.UserId
                   left join CustomContestLinks ccl on ccl.CustomContestId = s.CustomContestId
                   left join Contests c on c.ContestId=ccl.ContestId
                   left join Events evt on evt.ContestId = c.ContestId
                   where s.CustomContestId=@p1 AND ub.UserId=@p0 " +
                   "and evt.CloseDate < GETUTCDATE() "+
                   "and c.IsClosed=0", parameters3);
            foreach (UserObject sqlStats in maximumBetCountResult)
            {
                user.MaximumBetCount = sqlStats.MaximumBetCount;
            }

            // Récupération de l'évolution du score
            //IEnumerable<EventScores> userScores = (from userEventScore in dbContext.EventScores
            //                                       where (userEventScore.UserId == user.UserId)
            //                                       orderby userEventScore.Events.CloseDate
            //                          select userEventScore).ToList();
            object[] parameters4 = 
            {
                new SqlParameter { ParameterName = "p0", Value = user.UserId },
                new SqlParameter { ParameterName = "p1", Value = user.CustomContestId }
            };
            IEnumerable<RankObject> ranksEvolution = dbContext.ExecuteStoreQuery<RankObject>
                (@"select CurrentRank as Rank from CustomContestEventScores where UserId=@p0 AND CustomContestId=@p1", parameters4);
            foreach (RankObject rank in ranksEvolution)
            {
                user.RankEvolution.Add(rank.Rank);
            }

            return user;
        }

        /// <summary>
        /// Chargement d'un joueur à partir de son login
        /// </summary>
        /// <param name="userName">Login de l'utilisateur</param>
        /// <returns></returns>
        public static UserObject LoadUser(MyBetDBEntities dbContext, string userName)
        {
            UserObject user = UserObject.CopyFrom(dbContext, userName,null);
            return user;
        }

    }
}